if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spFdsNCleanUpOrphanedAccess]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spFdsNCleanUpOrphanedAccess]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/***************************************************************************
* Name:			spFdsDNCleanUpOrphanedAccess
* Purpose:		Remove access entries for deleted protocol staff
*
* PARAMETERS
* Name				Description					
* -------------		-----------------------------------------------------------
* n/a
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
* @_exitStatus		An exit status of either true or false
***************************************************************************/

CREATE PROCEDURE dbo.spFdsNCleanUpOrphanedAccess
AS
BEGIN

BEGIN TRANSACTION spFdsNCleanUpOrphanedAccess	

	DECLARE	@SUCCESS	AS BIT
	DECLARE	@FAILURE	AS BIT
	DECLARE @_UPDATE AS BIT

	SET	@SUCCESS = 0
	SET	@FAILURE = 1
	SET @_UPDATE = 1

   --- Access related information for any protocol staff removed should be cleard from the access management tables
   --- This sproc removes access information from the FwkAccessProvision table and the FwkResource_DomainPrincipal_Map 
   --- for any orphaned entries in these tables.
   
	DELETE FROM FwkAccessProvision
	where PrivilegedPrincipalId in  (
	SELECT id  from FwkResource_DomainPrincipal_Map fdm
	WHERE NOT EXISTS ( select 1 
					   from SvcEntity_AdmStaffedPerson_Map sam
					   inner join SvcEntity se
					   on sam.SvcEntityId = se.Id
					   inner join AdmPerson ap
					   on sam.AdmPersonId = ap.Id
					   inner join FwkDomainUser fdu
					   on ap.FwkDomainUserId = fdu.Id
					   where fdm.FwkResourceId = se.FwkResourceId
						and fdm.FwkDomainPrincipalId = fdu.FwkPrincipalId
						
					   UNION 

					   select 1
					   from PrtAccessControlRules pcr
					   inner join FwkDomainPrincipal fdp
					   on pcr.FwkPrincipalId = fdp.Id 	
					   inner join FwkResource fr 
					   on fr.Id = fdm.FwkResourceId
					   where  fdm.FwkDomainPrincipalId = fdp.Id	
		)
	)
	
	if ( @@ERROR > 0 ) GOTO ERROR_HANDLER
	
	DELETE from FwkResource_DomainPrincipal_Map 
	WHERE id in (
	SELECT id  from FwkResource_DomainPrincipal_Map fdm
	WHERE NOT EXISTS ( select 1 
					   from SvcEntity_AdmStaffedPerson_Map sam
					   inner join SvcEntity se
					   on sam.SvcEntityId = se.Id
					   inner join AdmPerson ap
					   on sam.AdmPersonId = ap.Id
					   inner join FwkDomainUser fdu
					   on ap.FwkDomainUserId = fdu.Id
					   where fdm.FwkResourceId = se.FwkResourceId
						and fdm.FwkDomainPrincipalId = fdu.FwkPrincipalId
						
					   UNION 

					   select 1
					   from PrtAccessControlRules pcr
					   inner join FwkDomainPrincipal fdp
					   on pcr.FwkPrincipalId = fdp.Id 	
					   inner join FwkResource fr 
					   on fr.Id = fdm.FwkResourceId
					   where  fdm.FwkDomainPrincipalId = fdp.Id	
		)
	)
	
	if ( @@ERROR > 0 ) GOTO ERROR_HANDLER

SET NOCOUNT OFF
COMMIT TRANSACTION  spFdsNCleanUpOrphanedAccess
RETURN @SUCCESS

ERROR_HANDLER: 
  SET NOCOUNT OFF
  ROLLBACK TRANSACTION spFdsNCleanUpOrphanedAccess
 RETURN @FAILURE

END

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

